package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.cwgl.Khzhsfmx;
import com.hnkywl.sts.entity.cwgl.Kmdm;
import com.hnkywl.sts.entity.sys.Yg;
import com.hnkywl.sts.entity.sys.Yjwzbm;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-3-23
 * Time: 下午1:37
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class KmdmDao extends SimpleHibernateTemplate<Kmdm, Long> {

    public List<Kmdm> findKmdmByGs(Long gsId) {
        String hql = "from Kmdm where nbgs_id = ?";
        return find(hql, gsId);
    }

    /**
     * 查询科目代码列表
     *
     * @param gsId     公司名称
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Map> findAllKmdm(Long gsId, String name, String code, int pageNo, int pageSize) {
        String sqlCount = "select count(1) from (" +
                "select km.*," +
                " connect_by_root name rootname," +
                " connect_by_isleaf isleaf," +
                " sys_connect_by_path(name,'/') pathname " +
                "from (select * from t_sys_kmdm where nbgs_id=:gsid or :gsid=-1) km " +
                " start with sjcode='dl' connect by sjcode=prior code " +
                " ) x where (:code='-1' or x.code like :code||'%') and (:name='-1' or x.name like '%'||:name||'%')";
        String sql = "select id \"id\",code \"code\",name \"name\",zjm \"zjm\", kmfx \"kmfx\",kmsx \"kmsx\"," +
                "kmdl \"kmdl\",hbhslx \"hbhslx\",qywl \"qywl\",fzhslx \"fzhslx\",sfxj \"sfxj\",pathname \"pathname\" " +
                "from (select km.*," +
                " connect_by_root name rootname," +
                " connect_by_isleaf isleaf," +
                " sys_connect_by_path(name,'/') pathname " +
                "from (select * from t_sys_kmdm where nbgs_id=:gsid or :gsid=-1) km " +
                " start with sjcode='dl' connect by sjcode=prior code " +
                " ) x where (:code='-1' or x.code like :code||'%') and (:name='-1' or x.name like '%'||:name||'%') " +
                " order by code asc ";
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        Map<String, Object> param = new HashMap<String, Object>();
        //==============条件参数拼接===============
        if (gsId == null)
            gsId = -1L;
        param.put("gsid", gsId);
        if (StringUtils.isEmpty(code))
            code = "-1";
        param.put("code", code);
        if (StringUtils.isEmpty(name))
            name = "-1";
        param.put("name", name);
        //==============记录数量===============
        SQLQuery query = createSQLQuery(sqlCount);
        query.setProperties(param);
        BigDecimal count = (BigDecimal) query.uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() == 0) {
            return page;
        }
        //==============查询结果===============
        query = createSQLQuery(sql);
        query.setProperties(param);
        query.setFirstResult((pageNo - 1) * pageSize);
        query.setMaxResults(pageSize);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> result = query.list();
        page.setResult(result);
        return page;
    }

    public Page<Map> findKmdmXm(Long gsId, int pageNo, int pageSize) {
        String sqlCount = " select count(1) from t_sys_kmdm where fzhslx = '项目核算' and deleted = 0 and nbgs_id = :gsid ";
        String sql = " select id \"id\",code \"code\",name \"name\",fzhslx as \"fzhslx\" " +
                " from t_sys_kmdm where fzhslx = '项目核算' and deleted = 0 and nbgs_id = :gsid";
        Page<Map> page = new Page<Map>(pageNo, pageSize, 0);
        Map<String, Object> param = new HashMap<String, Object>();
        //==============条件参数拼接===============
        if (gsId == null)
            gsId = -1L;
        param.put("gsid", gsId);
        //==============记录数量===============
        SQLQuery query = createSQLQuery(sqlCount);
        query.setProperties(param);
        BigDecimal count = (BigDecimal) query.uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() == 0) {
            return page;
        }
        //==============查询结果===============
        query = createSQLQuery(sql);
        query.setProperties(param);
        query.setFirstResult((pageNo - 1) * pageSize);
        query.setMaxResults(pageSize);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map> result = query.list();
        page.setResult(result);
        return page;
    }


    /**
     * 查询科目代码
     *
     * @param gsId     公司名称
     * @param code     科目代码
     * @param pageSize
     * @return
     */
    public Page<Kmdm> findKmdm(Long gsId, String code, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        List<String> condition = new ArrayList<String>();
        Page<Kmdm> page = new Page<Kmdm>(1, pageSize, 0);
        //==============条件拼接===============
        if (gsId != null) {
            condition.add(" nbgs.id = ? ");
            param.add(gsId);
        }
        if (code != null) {
            condition.add(" code like ? ");
            param.add(code.trim() + "%");
        }
        //==============查询结果===============
        hql = "from Kmdm ";
        if (condition.size() != 0) {
            hql = hql + " where " + StringUtils.join(condition, " and ");
            return find(page, hql, param.toArray());
        }
        return find(page, hql);
    }

    public Kmdm findKmdmDetailByGs(Long gsId, String code) {
        String hql = "from Kmdm where nbgs.id = ? and code = ?";
        return find(hql, gsId, code).size()<=0?null:(Kmdm) find(hql, gsId, code).get(0);
    }

    /**
     * 方法名:     getKmdmTreeJson
     * 功能描述:   返回科目代码树的Json字符串
     * 输入参数:
     * 返回值:     @return String {id:id,name:name,level:level,children:[]}
     * 异常:      <按照异常名字的字母顺序>
     * 创建人:     ccr
     * 创建日期:   13-2-19 下午16:59
     * 修改人:     ccr
     * 修改日期:   13-2-19 下午16:59
     */
    public String getKmbmTreeJson(String sjcode, String kmdl, Long gsId) {
        try {
            String nodes = "[";
            StringBuffer json = new StringBuffer();
            String hql = "from Kmdm where sjcode = ? and kmdl = ? and nbgs_id = ? order by code ";
            if (sjcode == null) {
                sjcode = "";
            }
            List<Kmdm> result = find(hql, sjcode, kmdl, gsId);
            if (result != null && !result.isEmpty()) {
                for (int i = 0; i < result.size(); i++) {
                    Kmdm k = result.get(i);
                    json.append("{\"id\":\"");
                    json.append(k.getCode()).append(":").append(kmdl);
                    json.append("\",\"text\":\"");
                    json.append(k.getName());
                    json.append("\",\"attributes\":{\"code\":\"" + k.getCode() + "\"}");
                    if (k.getSfxj()) {
                        json.append(",\"children\":[]");
                        json.append(",\"state\":\"closed\"");
                    }
                    json.append("},");
                }
                nodes += json.substring(0, json.length() - 1);
            }
            nodes += "]";
            return nodes;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "";
    }

    public void createSjcode(Long gsId) {
        String dl = "'dl'";
        String sql = "update t_sys_kmdm" +
                " set SJCODE=(case when length(code)!=4 then substr(code,1,length(code)-2) else " + dl + " end) where nbgs_id =" + gsId + "";
        executeSQL(sql);
    }

    public void createSfxj(Long gsId) {
        String sql = "update t_sys_kmdm t2 set t2.SFXJ = " +
                "  (case when exists (select 1 from t_sys_kmdm t1 where t1.sjcode = t2.code and t1.nbgs_id = ? ) then 1 else   0 end) " +
                "   where t2.nbgs_id =" + gsId + "";
        executeSQL(sql, gsId);
    }



    public void createKmdl(Long gsId) {
        String sql = " update t_sys_kmdm t2 set t2.KMDL = " +
                "  t2.KMDL || \'类\' " +
                "   where t2.nbgs_id =" + gsId + "";
        executeSQL(sql);
    }

    public void updateFzhslx(Long gsId) {
        String sql = "  update t_sys_kmdm set fzhslx  = " +
                " ( select zhb.fzhs from T_SYS_FZHSZHB zhb where yfzhs = fzhslx ) " +
               " where fzhslx is not null and nbgs_id =" + gsId+"" ;
        executeSQL(sql);
    }

    public String findKmPathName(Long gsid, String code) {
        String sql = "select distinct level,km.code,km.name from (select * from t_sys_kmdm where nbgs_id=? and deleted=0) km " +
                " start with km.code=? connect by prior km.sjcode = km.code " +
                " order by level desc ";
        List<Object[]> list = createSQLQuery(sql, gsid, code).list();
        List<String> path = new ArrayList<String>();
        if (list != null && !list.isEmpty()) {
            for (Object[] temp : list) {
                if (temp[2] != null)
                    path.add((String) temp[2]);
            }
        }
        return StringUtils.join(path, "/");
    }
}
